/* 
 * Copyright (C) 2014 Krzysztof Troska <elleander86 at gmail.com>
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 */
package anidbjclient;

import java.io.File;
import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;
import java.nio.file.Files;
import java.nio.file.InvalidPathException;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Handles all data, extends AnidbHandler from witch we get anidb info if needed
 * @author Krzysztof Troska <elleander86 at gmail.com>
 */
public class Database extends AnidbHandler {
    /**
     * Basic db driver (sqlite)
     */
    public static final String DRIVER = "org.sqlite.JDBC";
    /**
     * Link to db sqlite type, ani.db file
     */
    public static final String DB_URL = "jdbc:sqlite:ani.db";

    /**
     * creates AnidbHandler instance - we use that many times.
     */
    private static final AnidbHandler ah = new AnidbHandler();
    /**
     * Conection to local database we declare it static but you still need to call constructor to get connection
     */
    private static Connection conn = null;

    /**
     * Creates connection with local db, if connection can't be established we get fatal error and exit
     */
    public Database() {
        try {
            Class.forName(Database.DRIVER);
        } catch(ClassNotFoundException e) {
            System.err.println("No JDBC driver");
            System.exit(0);
        }
        try {
            if(conn == null)
                conn = DriverManager.getConnection(DB_URL);
        } catch (SQLException e) {
            System.err.println("Could not connect to the db, can't continue.");
            System.exit(0);
        }
        if(!createDatabase()) {
            System.err.println("Could not create local db, can't continue.");
            System.exit(0);
        }
    }
    /**
     * Creates local tables for later use, we DO NOT use foreign key because *reasons*.
     * @return true on success
     */
    private boolean createDatabase() {
        String anime = "CREATE TABLE IF NOT EXISTS anime (aid INTEGER PRIMARY KEY, name STRING, episodes INTEGER, anime_type STRING, adult BOOLEAN, "
                + "date_flag STRING, year STRING, air_date INTEGER, end_date INTEGER, pic_name STRING);";
        String files = "CREATE TABLE IF NOT EXISTS files (fid INTEGER PRIMARY KEY, aid INTEGER, eid INTEGER, gid INTEGER, "
                        + "size INTEGER, ed2k STRING, filename STRING, path STRING);";
        String groups = "CREATE TABLE IF NOT EXISTS groups (gid INTEGER PRIMARY KEY, name STRING);";
        String other_files = "CREATE TABLE IF NOT EXISTS other_files (ed2k STRING UNIQUE, path STRING);";
        String files_to_add = "CREATE TABLE IF NOT EXISTS files_to_add (ed2k STRING, path String UNIQUE, fid INTEGER);";
        String description = "CREATE TABLE IF NOT EXISTS description (aid INTEGER UNIQUE, description TEXT);";
        String myList = "CREATE TABLE IF NOT EXISTS my_list (my_list_id INTEGER PRIMARY KEY, fid INTEGER, my_list_state INTEGER, "
                + "my_list_file_state INTEGER, viewed_date INTEGER);";
        String episodes = "CREATE TABLE IF NOT EXISTS episodes (eid INTEGER PRIMARY KEY, aid INTEGER, air_date INTEGER, ep_number "
                + "STRING, type INTEGER, ep_name STRING, ep_name_en STRING);";
        try (Statement stat = conn.createStatement()) {
            stat.execute(anime);    
            stat.execute(files);
            stat.execute(groups);
            stat.execute(other_files);
            stat.execute(files_to_add);
            stat.execute(description);
            stat.execute(myList);
            stat.execute(episodes);
            return true;
        } catch(SQLException e) {
            System.err.println("Base Creation Failed");
            return false;
        }
    }

    /**
     * Returns a map with full info from anime db all Lists contains object of their
     * respective column type (String, Int etc.) but in Object container!
     * @param columns Array of strings containing columns names we want to select from db either only nam "eid" or 
     * "table.column AS column" for example: episodes.eid AS eid.
     * @param database String inserted after FROM in sql stetment for example: "episodes;", "episodes ORDER BY eid" etc.; 
     * @return map containing full info from files db Map "column name", List"Objects"
     */
    @SuppressWarnings("empty-statement")
    public Map<String, List<Object>> getDatabaseInfo(String[] columns, String database) {
        ResultSet rs = null;
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            /** no point using prepStmt since it will throw exception if there is no db name inside */
            String query;
            if(columns.length > 1) {
                StringBuilder sb = new StringBuilder();
                for(int i = 0; i<columns.length; i++) {
                    sb.append(columns[i]);
                    if(i == columns.length - 1)
                        break; // we dont want string to end with ,
                    sb.append(", ");
                }
                query = "SELECT "+ sb.toString() +" FROM " + database;
            } else
                query = "SELECT "+ columns[0] +" FROM " + database;
            rs = stmt.executeQuery(query);
            /** Create map and insert into it */
            Map<String, List<Object>> result = new HashMap<>();
            /** After sending columns into sql query we want to change them into normal */
            for(int i =0; i<columns.length; i++) {
                if(columns[i].contains(" "))
                    columns[i] = columns[i].substring(columns[i].lastIndexOf(" ") +1);
            }
            /** put the keys into map so we can later put values containing info from db with good map name */
            for(String name : columns)
                result.put(name, new ArrayList<>());
            while(rs.next()) {
                putRowIntoMap(result, rs, columns);
            }
            return result;
        } catch(SQLException e) {
            System.err.println("Database Error in getDatabaseInfo(String[], String).");
            return null;
        } finally {
            try { if (rs != null) rs.close(); } catch (SQLException e) {};
            try { if (stmt != null) stmt.close(); } catch (SQLException e) {};
        }
    }
    
    /**
     * Puts one row of results into given map acording to list
     * @param map map for results taken from db
     * @param rs result set with one row line
     * @param list list of colums we want to read
     * @return map wit added one row from db
     * @throws SQLException 
     */
    private Map<String, List<Object>> putRowIntoMap(Map<String, List<Object>> map, ResultSet rs, String[] list) throws SQLException {
        /** list of columns which have integer values */
        final List<String> integers = Arrays.asList("aid", "total", "eid", "fid", "gid", "size", "viewed_date",
                "type", "episodes", "air_date", "end_date", "my_list_id", "my_list_state", "my_list_file_state", "owned_episodes");
        /** list of columns which have boolean values */
        final List<String> booleans = Arrays.asList("adult", "not_watched");
        for(String name : list) {
            if(integers.contains(name)) // read as int
                map.get(name).add(rs.getInt(name));
            else if(booleans.contains(name)) // read as boolean
                map.get(name).add(rs.getBoolean(name));
            else // read as string(default)
                map.get(name).add(rs.getString(name));
        }
        return map;
    }
    
    /**
     * Get episode Info by given aid
     * @param aid anime id
     * @return map with nearly all info from db about given anime
     */
    public Map<String, List<Object>> getEpisodeInfo(int aid) {
        String database = "my_list, episodes, files WHERE my_list.fid = files.fid AND files.eid = episodes.eid"
                + " AND episodes.aid = "+ aid +" ORDER BY episodes.type, episodes.ep_number;";
        String[] columns = {"episodes.eid AS eid", "episodes.ep_number AS ep_number", "episodes.type AS type", 
            "episodes.ep_name AS ep_name", "episodes.ep_name_en AS ep_name_en", "my_list.viewed_date AS viewed_date"};
        return this.getDatabaseInfo(columns, database);
    }
    /**
     * Returns path to the first episode of selected anime name and ep number, can contain more than one 
     * file info if we got same episode from different groups
     * @param eid episode id
     * @return list of object[0] - fid, object[1] - Path
     */
    @SuppressWarnings("empty-statement")
    public List<Object[]> getEpisodePath(int eid) {
        ResultSet rs = null;
        try (PreparedStatement prepStmt = conn.prepareStatement("SELECT files.path, files.fid FROM files, "
                + "episodes WHERE files.eid = episodes.eid AND episodes.eid = ?;")){
            prepStmt.setInt(1, eid);
            rs = prepStmt.executeQuery();
            List<Object[]> list = new ArrayList<>();
            try {
                while(rs.next()) {
                    Object[] tmp = new Object[2];
                    tmp[0] = rs.getInt("fid");
                    tmp[1] = Paths.get(rs.getString("path"));
                    list.add(tmp);
                }
            } catch(InvalidPathException ex) {
                System.err.println("Internal db error.");
            }
            return list;
        } catch (SQLException e) {
            System.err.println("Could not get path from db");
            return null;
        } finally {
            try { if (rs != null) rs.close(); } catch (SQLException e) {};
        }
    }
    
    /**
     * Handles new file check if its anidb file we download all info about it and put it in local db than return fid.
     * @param path - path of a file we want to add
     * @param hash - ed2k of file we want to add
     * @return fid if success and -1 on error
     */
    @SuppressWarnings("empty-statement")
    public int processNewFile(Path path, String hash) {
        System.out.println("Processing new file: " + path.getFileName());
        ResultSet rs = null;
        /** Check if file isn't already in local db */
        try (PreparedStatement prepStmt = conn.prepareStatement("SELECT COUNT(*) AS total, fid FROM files WHERE ed2k= ?;")) {   
            prepStmt.setString(1, hash);
            rs = prepStmt.executeQuery();
            rs.next();
            if(rs.getInt("total") >0) {
                return rs.getInt("fid");
            }
        } catch (SQLException ex) {
            System.err.println("Can't check if file isn't in db already processNewFile(Path, hash)");
        } finally {
            try { if (rs != null) rs.close(); } catch (SQLException e) {};
        }
        /** Try to import data from anidb, if not in anidb add to other files if no connection add to files_to_add */
        String[] tmp;
        try {
            tmp = ah.importFileInfo(path, hash);
        } catch (AnidbMissingContentException e) {
            System.err.println("No such file in anidb inserting into other files.");
            this.insertOtherFiles(path, hash);
            return -1;
        } catch (AnidbException ex) {
            System.err.println("Cant get data from anidb.");
            return -1;
        }
        
        /** get fid from anidb if by some means fid is bad we don't want to add file */
        int fid;
        try {
            fid = Integer.parseInt(tmp[0]);
        } catch (NumberFormatException ex) {
            System.err.println("Can't get int from fid. tmp[0]" + tmp[0]);
            return -1;
        }
        if(!this.insertFile(path.toFile(), tmp)) {
            return -1;
        }
        
        /** 
         * More inserts 
         * Insert group thx to data we get from importFileInfo() 
         * those try catch should not happend but still better check than sorry.
         */
        this.insertGroup(tmp);
        
        /** Import anime info from anidb and insert into local db */
        try {
            this.insertAnime(Integer.parseInt(tmp[1]));
        } catch (NumberFormatException ex) {
            System.err.println("Can't get int from aid. tmp[1]" + tmp[1]);
        }
        
        /** Import episode info from anidb and insert into local db */
        try {
            this.insertEpisode(Integer.parseInt(tmp[2]));
        } catch (NumberFormatException ex) {
            System.err.println("Can't get int from gid. tmp[2]" + tmp[2]);
        }
        /** 
         * If file was alredy in my list we use data from importFileInfo() if not 
         * we add file to my list with default values
         */
        try {
            if(Integer.parseInt(tmp[4]) > 0)
                this.insertMyList(tmp);
            else
                this.insertMyList(fid);
        } catch (NumberFormatException ex) {
            System.err.println("Can't get int from myListId. tmp[4]: " + fid);
            /** 
             * we insert new info in case anidb returned wrong number in tmp[4] 
             */
            this.insertMyList(fid); 
        }
        return fid;
    }

    /**
     * Get anime picture path from local db in case we dont have local cache we download file from anidb
     * @param aid id of anime we want pic
     * @return path to file on local drive or null when error
     */
    @SuppressWarnings("empty-statement")
    public Path getAnimePic(int aid) {
        Path dest = Paths.get(new File(".").getAbsolutePath(), "pics");
        if(!dest.toFile().exists() && !dest.toFile().mkdirs()) {
            System.err.println("Can't create pics directory in getAnimePic()");
            return null;
        }
        ResultSet rs = null;
        try (PreparedStatement prepStmt = conn.prepareStatement("SELECT pic_name AS name FROM anime WHERE aid= ? ;")) {
            prepStmt.setInt(1, aid);
            rs = prepStmt.executeQuery();
            String picName = rs.getString("name");
            dest = Paths.get(dest.toString(), picName);
            if(dest.toFile().exists())
                return dest;
            URL website = new URL("http://img7.anidb.net/pics/anime/"+ picName);
            Files.copy(website.openStream(), dest);
            return dest;
        } catch (MalformedURLException ex) {
            System.err.println("Wrong aid in getAnimePic()");
            return null;
        } catch (IOException ex) {
            System.err.println("Can't download anime pic in getAnimePic()");
            return null;
        } catch (SQLException ex) {
            System.err.println("Can't get pic information in getAnimePic()");
            return null;
        }finally {
            try { if (rs != null) rs.close(); } catch (SQLException e) {};
        }     
    }
    
    /**
     * Gets raw anime description from local db in canse we have none we geting one from anidb.
     * @param aid anime id
     * @return String containing raw description
     */
    @SuppressWarnings("empty-statement")
    public String getAnimeDescription(int aid) {
        ResultSet rs = null;
        PreparedStatement prepStmt = null;
        try {
            prepStmt = conn.prepareStatement("SELECT COUNT(*) AS total, description FROM description WHERE aid= ? ;");
            prepStmt.setInt(1, aid);
            rs = prepStmt.executeQuery();
            if(rs.getInt("total")==1){
                return rs.getString("description");
            }
            String description = ah.importAnimeDescription(aid);
            prepStmt = conn.prepareStatement("INSERT INTO description VALUES (?, ?);");
            prepStmt.setInt(1,aid);
            prepStmt.setString(2, description);
            prepStmt.execute();
            return description;
        } catch (AnidbException e) {
            System.err.println("Can't get Anime Description AnidbException.");
            return "Can't get Anime Description";                
        } catch (SQLException e) {
            System.err.println("Can't get Anime Description SqlException.");
            return "Can't get Anime Description";
        } finally {
            try { if (rs != null) rs.close(); } catch (SQLException e) {};
            try { if (prepStmt != null) prepStmt.close(); } catch (SQLException e) {};
        }
    }

    /**
     * Updates episode path in local DB.
     * @param path - NEW path to file
     * @param fid id of the file we want to change path
     * @return true if changed false if db error
     */
    public boolean updateEpisodePath(Path path, int fid) {
        try (PreparedStatement prepStmt = conn.prepareStatement("UPDATE files SET path = ? WHERE fid = ?;")) {
            prepStmt.setString(1, path.toString());
            prepStmt.setInt(2,fid);
            prepStmt.execute();
            return true;
        } catch (SQLException e) {
            System.err.println("Could not update path in db");
            return false;
        }
    }
    
    /**
     * Returns my list id for given fid
     * @param fid file id
     * @return my list id or -1 when none or error
     */
    @SuppressWarnings("empty-statement")
    public int getMyListId(int fid) {
        ResultSet rs = null;
        try (PreparedStatement prepStmt = conn.prepareStatement("SELECT my_list_id FROM my_list WHERE fid = ?;")){ 
            prepStmt.setInt(1, fid);
            rs = prepStmt.executeQuery();
            rs.next();
            return rs.getInt("my_list_id");
        } catch(SQLException e) {
            return -1;
        } finally {
            try { if (rs != null) rs.close(); } catch (SQLException e) {};
        }
    }
    
    /**
     * Chceck by path if file is in other files db its should be used BEFORE countig ed2k hash
     * @param path path to a file for check
     * @return true if is false if not
     */
    @SuppressWarnings("empty-statement")
    public boolean checkOtherFiles(Path path) {
        ResultSet rs = null;
        try (PreparedStatement prepStmt = conn.prepareStatement("SELECT COUNT(*) AS total FROM other_files WHERE path = ?;")){
            prepStmt.setString(1, path.toString());
            rs = prepStmt.executeQuery();
            return rs.getInt("total") > 0;
        } catch (SQLException e) {
            System.err.println("Cant get count in checkOtherFiles(Path).");
            return false;
        } finally {
            try { if (rs != null) rs.close(); } catch (SQLException e) {};
        }
    }
    
    /**
     * Chceck by path if file is in files_to_add db its should be used BEFORE countig ed2k hash
     * @param path path to a file for check
     * @return true if file is in files_to_add
     */
    @SuppressWarnings("empty-statement")
    public boolean checkFilesToAdd(Path path) {
        ResultSet rs = null;
        try (PreparedStatement prepStmt = conn.prepareStatement("SELECT COUNT(*) AS total FROM files_to_add WHERE path = ?;")){
            prepStmt.setString(1, path.toString());
            rs = prepStmt.executeQuery();
            return (rs.getInt("total") > 0);
        } catch (SQLException e) {
            System.err.println("Cant get count in checkOtherFiles(Path).");
            return false;
        } finally {
            try { if (rs != null) rs.close(); } catch (SQLException e) {};
        }
    }

    /**
     * Add file to my list in Anidb with status on hdd and watched at given time
     * @param fid - file id
     * @viewTime - time in unix time stamp of watch
     * @return true if success
     */
    private boolean insertMyList(int fid, long viewTime) {
        int myListId = getMyListId(fid);
        if(myListId > 0)
            return true;
        try (PreparedStatement prepStmt = conn.prepareStatement("INSERT INTO my_list VALUES (?, ?, ?, ?, ?);")) {
            myListId = ah.addToMyList(fid, -1);
            /** Prepere stmt to add default values into my_list db */
            prepStmt.setInt(1, myListId);
            prepStmt.setInt(2, fid);
            prepStmt.setInt(3, 1);
            prepStmt.setInt(4, 1);
            prepStmt.setLong(5, viewTime);
            prepStmt.execute();
            return true;
        } catch (AnidbException e) {
            System.err.println("Can't add file to my list.");
            return false;
        } catch (SQLException ex) {
            System.err.println("Can't add values into db in insertMyList(fid, viewTime).");
            return false;
        }
    }
    
     /**
     * Add file to my list in Anidb with default values not watched and status on hdd
     * @param fid - file id
     * @return true if success
     */
    private boolean insertMyList(int fid) {
        return insertMyList(fid, -1);
    }
    
    /**
     * Inserts my list info into db (file was alredy added to anidb)
     * @param arg array of Strings from importFileInfo()
     * @return true if success
     */
    private boolean insertMyList(String[] arg) {
        try (PreparedStatement prepStmt = conn.prepareStatement("INSERT OR REPLACE INTO my_list VALUES (?, ?, ?, ?, ?);")) {
            /** Prepere stmt to add values from anidb */
            prepStmt.setString(1, arg[4]); // my list id
            prepStmt.setString(2, arg[0]); // file id
            prepStmt.setString(3, arg[6]); // my list state
            prepStmt.setString(4, arg[7]); // my list file state
            try {
                int viewDate = Integer.parseInt(arg[8]);
                if(viewDate >0)
                    prepStmt.setInt(5, viewDate);
                else
                    prepStmt.setInt(5, -1);
            } catch (NumberFormatException e) {
                prepStmt.setInt(5, -1);
            }
            prepStmt.execute();
            return true;
        } catch (SQLException ex) {
            System.err.println("Can't add values into db in insertMyList(String[]).");
            return false;
        }
    }

    /**
     * Insert file into local db
     * @param file file to be inserted
     * @param arg array of strings in proper order (look AnidbHandler.importFileInfo())
     * @return true if success
     */
    private boolean insertFile(File file, String arg[]) {
        try (PreparedStatement prepStmt = conn.prepareStatement("INSERT OR REPLACE "
                    + "INTO files VALUES (?, ?, ?, ?, ?, ?, ?, ?);")){
            prepStmt.setInt(1, Integer.parseInt(arg[0])); //file id
            prepStmt.setInt(2, Integer.parseInt(arg[1])); //anime id
            prepStmt.setInt(3, Integer.parseInt(arg[2])); //episode id
            prepStmt.setInt(4, Integer.parseInt(arg[3])); //group id
            prepStmt.setLong(5, file.length());
            prepStmt.setString(6, arg[10]); // ed2k hash
            prepStmt.setString(7, file.getName());
            prepStmt.setString(8, file.getAbsolutePath());
            prepStmt.execute();
            return true;
        } catch(SQLException e) {
            System.err.println("Can't add value");
            return false;
        }     
    }
    
    /**
     * Insert new group into local db
     * @param arg array of strings in proper order (look AnidbHandler.importFileInfo())
     * @return true if success
     */
    @SuppressWarnings("empty-statement")
    private boolean insertGroup(String arg[]) {
        ResultSet rs = null;
        try (PreparedStatement prepStmt = conn.prepareStatement("INSERT OR REPLACE INTO groups VALUES (?, ?);")) {
            prepStmt.setInt(1, Integer.parseInt(arg[3])); //group id
            prepStmt.setString(2, arg[9]); //group name
            prepStmt.execute();
            return true;
        } catch(SQLException e) {
            System.err.println("Can't add value into db in insertGroup(String[])");
            return false;
        } catch(NumberFormatException ex) {
            System.err.println("Wrong group id in insertGroup(String[])");
            return false;
        }finally {
            try { if (rs != null) rs.close(); } catch (SQLException e) {};
        }
    }
    
    /**
     * Insert new group into local db, it makes use of data from AnidbHandler EPISODE api.
     * @param gid id of a group
     * @return true if success
     */
    @SuppressWarnings("empty-statement")
    private boolean insertGroup(int gid) {
        ResultSet rs = null;
        try (PreparedStatement prepStmt = conn.prepareStatement("INSERT OR REPLACE INTO groups VALUES (?, ?);")){
            String[] tmp = ah.importGroupInfo(gid);
            prepStmt.setString(1, tmp[0]); //group id
            prepStmt.setString(2, tmp[5]); //group name
            prepStmt.execute();
            return true;
        } catch(SQLException e) {
            System.err.println("Can't add value into db in insertGroup(int)");
            return false;
        } catch(NumberFormatException ex) {
            System.err.println("Wrong group id in insertGroup(int)");
            return false;
        } catch (AnidbException ex) {
            System.err.println("Can't get group information from anidb in insertGroup(int)");
            return false;
        }finally {
            try { if (rs != null) rs.close(); } catch (SQLException e) {};
        }
    }
    
    /**
     * Insert new anime title into local db
     * @param arg array of strings in proper order (look AnidbHandler.importFileInfo())
     * @return true if success
     */
    @SuppressWarnings("empty-statement")
    private boolean insertAnime(int aid) {
        PreparedStatement prepStmt = null;
        ResultSet rs = null;
        try {
            prepStmt = conn.prepareStatement("SELECT COUNT(*) AS total FROM anime "
                    + "WHERE aid = ?;");
            prepStmt.setInt(1, aid);
            rs = prepStmt.executeQuery();
            if(rs.getInt("total") > 0)
                return true;
            String[] arg = ah.importAnimeInfo(aid);
            prepStmt = conn.prepareStatement("INSERT INTO anime VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
            prepStmt.setInt(1, Integer.parseInt(arg[0])); // aid
            prepStmt.setString(2, arg[4]); //name
            prepStmt.setInt(3, Integer.parseInt(arg[5])); //episodes
            prepStmt.setString(4, arg[3]); //type
            if(Integer.parseInt(arg[9]) == 1)
                prepStmt.setBoolean(5, Boolean.TRUE); //adult
            else
                prepStmt.setBoolean(5, Boolean.FALSE); //adult
            prepStmt.setInt(6, Integer.parseInt(arg[1])); //date flags
            prepStmt.setString(7, arg[2]); //year
            prepStmt.setInt(8, Integer.parseInt(arg[6])); // air date
            prepStmt.setInt(9, Integer.parseInt(arg[7])); // end date
            prepStmt.setString(10, arg[8]); // pic name
            prepStmt.execute();
            return true;
        } catch(SQLException e) {
            System.err.println("Can't add value in insertAnime");
            return false;
        } catch (AnidbException ex) {
            System.err.println("Can't get anidb info FROM insertAnime(int)");
            return false;
        } finally {
            try { if (rs != null) rs.close(); } catch (SQLException e) {};
            try { if (prepStmt != null) prepStmt.close(); } catch (SQLException e) {};
        }
    }
    
    /**
     * Insert new episode info into local db
     * @param arg array of strings in proper order (look AnidbHandler.importFileInfo())
     * @return true if success
     */
    private boolean insertEpisode(int eid) {
        String[] arg;
        try {
            arg = ah.importEpisodeInfo(eid);
        } catch (AnidbException e) {
            System.err.println("Cant get episode info from anidb.");
            return false;
        }
        try (PreparedStatement prepStmt = conn.prepareStatement("INSERT INTO episodes VALUES (?, ?, ?, ?, ?, ?, ?);")) {
            prepStmt.setInt(1, Integer.parseInt(arg[0])); //eid
            prepStmt.setInt(2, Integer.parseInt(arg[1])); //aid
            prepStmt.setInt(3, Integer.parseInt(arg[9])); //air_date
            /** Change ep number into normal int */
            int type = Integer.parseInt(arg[10]);
            char typeChar = getSpecialChar(type);
            String epNumber = arg[5];
            if(!Character.isWhitespace(typeChar))
                epNumber = epNumber.replaceAll("[A-Z]", "");
            prepStmt.setString(4, epNumber);
            prepStmt.setInt(5, type); //type
            prepStmt.setString(6, arg[7]); //ep_name
            prepStmt.setString(7, arg[6]); //ep_name_en
            prepStmt.execute();
            return true;
        } catch(SQLException e) {
            System.err.println("Can't add value in insertEpisode(eid)");
            return false;
        }
    }
    
    /**
     * Returns special name used as anidb episode nr prefix 
     * @param type type number from 1-6
     * @return character acording to type or space
     */
    public String getSpecialString(int type) {
        switch(type) {
        case 1: return "Regular Episode"; // regular episode no prefix
        case 2: return "Special"; // special 
        case 3: return "Credit"; // credit
        case 4: return "Trailer"; // trailer
        case 5: return "Parody"; // parody
        case 6: return "Other"; // other
        default: return ""; // should not happend
        }
    }
    
    /**
     * Returns special character used as anidb episode nr prefix 
     * @param type type number from 1-6
     * @return character acording to type or space
     */
    private char getSpecialChar(int type) {
        switch(type) {
        case 1: return ' '; // regular episode no prefix
        case 2: return 'S'; // special 
        case 3: return 'C'; // credit
        case 4: return 'T'; // trailer
        case 5: return 'P'; // parody
        case 6: return 'O'; // other
        default: return ' '; // should not happend
        }
    }

    /**
     * Inserts file into other_files db so we won't have to count hash of this file many times
     * @param path path to file
     * @param hash ed2k hash
     * @return true if success or already inside
     */
    @SuppressWarnings("empty-statement")
    private boolean insertOtherFiles(final Path path, final String hash) {
        ResultSet rs = null;
        try (PreparedStatement prepStmt = conn.prepareStatement("INSERT OR REPLACE INTO other_files VALUES (?, ?);")){
            prepStmt.setString(1, hash); //ed2k hash
            prepStmt.setString(2, path.toString());
            prepStmt.execute();
            return true;
        } catch(SQLException e) {
            System.err.println("Can't add value in insertOtherFiles()");
            return false;
        } finally {
            try { if (rs != null) rs.close(); } catch (SQLException e) {};
        }
    }
    
    /**
     * Inserts or updates file into files_to_add db so we can add them to db when anidb is avaible
     * @param path to file
     * @param hash ed2k hash
     * @param fid id of a file in anidb
     * @return true if success or already inside
     */
    @SuppressWarnings("empty-statement")
    public boolean insertFilesToAdd(Path path, String hash, int fid) {
        ResultSet rs = null;
        try (PreparedStatement prepStmt = conn.prepareStatement("INSERT OR REPLACE INTO files_to_add VALUES (?, ?, ?);");) {
            prepStmt.setString(1, hash);
            prepStmt.setString(2, path.toString());
            prepStmt.setInt(3, fid);
            prepStmt.execute();
            return true;
        } catch(SQLException e) {
            System.err.println("Can't add value in insertFilesToAdd()");
            return false;
        } finally {
            try { if (rs != null) rs.close(); } catch (SQLException e) {};
        }
    }
    
    /**
     * Inserts or updates file into files_to_add db so we can add them to db when anidb is avaible,
     * sets fid to default -1 value so we know this file was not tested yet
     * @param path to file
     * @param hash ed2k hash
     * @return true if success or already inside
     */
    public boolean insertFilesToAdd(Path path, String hash) {
        return insertFilesToAdd(path, hash, -1);
    }
    
    /**
     * Inserts or updates file into files_to_add db so we can add them to db when anidb is avaible,
     * sets fid to default -1 and hash to 0 so we know this file was not tested yet.
     * @param path to file
     * @return true if success or already inside
     */
    public boolean insertFilesToAdd(Path path) {
        return insertFilesToAdd(path, "0", -1);
    }
    
    /**
     * Set current time as watch time for a given fid
     * @param fid id of a file we want to mark watched
     * @return 
     */
    @SuppressWarnings("empty-statement")
    public boolean markWatched(int fid) {
        ResultSet rs = null;
        PreparedStatement prepStmt = null;
        long viewTime = System.currentTimeMillis()/1000;
        try {
            prepStmt = conn.prepareStatement("SELECT COUNT(*) AS "
                + "total, viewed_date, my_list_id FROM my_list WHERE fid = ?;");
            prepStmt.setInt(1, fid);
            rs = prepStmt.executeQuery();
            if(rs.getInt("total") == 0)
                return insertMyList(fid, viewTime);
            else if(rs.getInt("viewed_date")>0)
                return true;
            int lid = rs.getInt("my_list_id");
            prepStmt = conn.prepareStatement("UPDATE my_list SET viewed_date = ? "
                    + "WHERE fid = ?;");
            prepStmt.setLong(1, viewTime);
            prepStmt.setInt(2, fid);
            /** updateing anidb and than we update local db so we wont get different values. */
            ah.editMyList(lid, 1, viewTime);
            prepStmt.execute();
            return true;
        } catch(SQLException e) {
            System.err.println("Can't mark watched SQLException in markWatched(int)");
            return false;
        } catch (AnidbException ex) {
            System.err.println("Cant update file info in anidb markWatched(int)");
            return false;
        } finally {
            try { if (rs != null) rs.close(); } catch (SQLException e) {};
            try { if (prepStmt != null) prepStmt.close(); } catch (SQLException e) {};
        }
    }
    
    /**
     * Downloads all missing entries in databases my list, anime, group, episodes
     * @return true if success
     */
    public boolean repairDatabase() {
        boolean response = true;
        if(!repairMyList())
            response = false;
        if(!repairAnime())
            response = false;
        if(!repairEpisodes())
            response = false;
        if(!repairGroup())
            response = false;
        return response;
    }
    
    /**
     * Adds ALL missing files to my list
     * @return true if success
     */
    @SuppressWarnings("empty-statement")
    private boolean repairMyList() {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT fid FROM files WHERE fid NOT IN (SELECT files.fid "
                    + "FROM files, my_list WHERE files.fid = my_list.fid);");
            while(rs.next())
                insertMyList(rs.getInt("fid"));
            return true;
        } catch(SQLException e) {
            System.err.println("Database Error in getAnimeInfo(fid).");
            return false;
        } finally {
            try { if (rs != null) rs.close(); } catch (SQLException e) {};
            try { if (stmt != null) stmt.close(); } catch (SQLException e) {};
        }
    }
    
    /**
     * Adds all missing anime informations
     * @return true if success
     */
    @SuppressWarnings("empty-statement")
    private boolean repairAnime() {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT aid FROM files WHERE aid NOT IN (SELECT "
                    + "anime.aid FROM files, anime WHERE files.aid = anime.aid);");
            while(rs.next())
                insertAnime(rs.getInt("aid"));
            return true;
        } catch(SQLException e) {
            System.err.println("Database Error in getAnimeInfo(fid).");
            return false;
        } finally {
            try { if (rs != null) rs.close(); } catch (SQLException e) {};
            try { if (stmt != null) stmt.close(); } catch (SQLException e) {};
        }
    }
    
    /**
     * Adds all missing episodes informations
     * @return true if success
     */
    @SuppressWarnings("empty-statement")
    private boolean repairEpisodes() {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT eid FROM files WHERE eid NOT IN (SELECT "
                    + "episodes.eid FROM files, episodes WHERE files.eid = episodes.eid);");
            while(rs.next())
                insertEpisode(rs.getInt("eid"));
            return true;
        } catch(SQLException e) {
            System.err.println("Database Error in getAnimeInfo(fid).");
            return false;
        } finally {
            try { if (rs != null) rs.close(); } catch (SQLException e) {};
            try { if (stmt != null) stmt.close(); } catch (SQLException e) {};
        }
    }
    
    /**
     * Adds all missing group informations
     * @return true if success
     */
    @SuppressWarnings("empty-statement")
    private boolean repairGroup() {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT gid FROM files WHERE gid NOT IN (SELECT "
                    + "groups.gid FROM files, groups WHERE files.gid = groups.gid);");
            while(rs.next())
                insertGroup(rs.getInt("gid"));
            return true;
        } catch(SQLException e) {
            System.err.println("Database Error in getAnimeInfo(fid).");
            return false;
        } finally {
            try { if (rs != null) rs.close(); } catch (SQLException e) {};
            try { if (stmt != null) stmt.close(); } catch (SQLException e) {};
        }
    }
    
    /**
     * Trying to insert files from files_to_add database
     * @return true if success
     */
    @SuppressWarnings("empty-statement")
    public boolean insertLater() {
        Statement stmt = null;
        ResultSet rs = null;
        String hash = null;
        while(true)
            try {
                stmt = conn.createStatement();
                rs = stmt.executeQuery("SELECT * FROM files_to_add;");
                while(rs.next()){
                    hash = rs.getString("hash");
                    Path path = Paths.get(rs.getString("path"));
                    /** check if its realy file (and if it still exist)*/
                    if(!path.toFile().isFile())
                        deleteFromFilesToAdd(hash);
                    /** try to add this file into local db calls delete from files to add inside process new file */
                    processNewFile(path, hash);
                }
                return true;
            } catch(InvalidPathException e) {
                System.err.println("Invalis Path Exception in getAnimeInfo(fid).");
                deleteFromFilesToAdd(hash);
                /**
                 * TODO: after this stmt and rs get closed and while goes once more 
                 * we want to change this so stmt and rs don't get closed
                 */
            } catch(SQLException e) {
                System.err.println("Database Error in insertLater().");
                return false;
            } finally {
                try { if (rs != null) rs.close(); } catch (SQLException e) {};
                try { if (stmt != null) stmt.close(); } catch (SQLException e) {};
            }
    }
    
    /**
     * Removes entry from FilesToAdd by given hash values
     * @param hash ed2k hash of file we want to delete
     * @return true if success
     */
    public boolean deleteFromFilesToAdd(String hash) {
        try(PreparedStatement prepStmt = conn.prepareStatement("DELETE FROM files_to_add WHERE ed2k = ?;")) {
            prepStmt.setString(1, hash);
            prepStmt.execute();
            return true;
        } catch (SQLException e) {
            System.err.println("Database Error in deleteFromFilesToAdd(String).");
            return false;
        }
    }
    
    /**
     * Removes entry from FilesToAdd by given path
     * @param path to a file we want to delete from files_to_add db.
     * @return true if success
     */
    public boolean deleteFromFilesToAdd(Path path) {
        try(PreparedStatement prepStmt = conn.prepareStatement("DELETE FROM files_to_add WHERE path = ?;")) {
            prepStmt.setString(1, path.toString());
            prepStmt.execute();
            return true;
        } catch (SQLException e) {
            System.err.println("Database Error in deleteFromFilesToAdd(Path).");
            return false;
        }
    }

    /**
     * Returns fid of file with given path
     * @param path - path to a file we want fid of
     * @return fid or -1 when error
     */
    public int getFilesToAddFid(Path path) {
        String[] columns = {"COUNT(*) AS total", "fid"};
        String database = "files_to_add WHERE path='" + path.getFileName().toString().replaceAll("'", "''") + "';";
        Map<String, List<Object>> databaseInfo = this.getDatabaseInfo(columns, database);
        if((int)databaseInfo.get("total").get(0) > 0)
            return (int)databaseInfo.get("fid").get(0);
        return -1;
    }

    /**
     * Update file id parametr in files to add
     * @param path path to a file we want to upgrade
     * @param fid new file id of file.
     */
    public void updateFilesToAdd(Path path, int fid) {
        try(PreparedStatement prepStmt = conn.prepareStatement("UPDATE files_to_add SET fid = ? WHERE path = ?;")) {
            prepStmt.setInt(1, fid);
            prepStmt.setString(2, path.toString());
            prepStmt.execute();
        } catch (SQLException e) {
            System.err.println("Database Error in updateFilesToAdd(path, fid).");
        }
    }
    
    /**
     * Update hash parameter parametr in files to add
     * @param hash - new hash for file
     * @param path - path to a file 
     */
    public void updateFilesToAdd(Path path, String hash) {
        try(PreparedStatement prepStmt = conn.prepareStatement("UPDATE files_to_add SET ed2k = ? WHERE path = ?;")) {
            prepStmt.setString(1, hash);
            prepStmt.setString(2, path.toString());
            prepStmt.execute();
        } catch (SQLException e) {
            System.err.println("Database Error in updateFilesToAdd(path, hash).");
        }
    }

    public void deleteEmptyFromFilesToAdd() {
        try(Statement stat = conn.createStatement()) {
            stat.execute("DELETE FROM files_to_add WHERE ed2k='0';");
        } catch (SQLException e) {
            System.err.println("Database Error in updateFilesToAdd(path, hash).");
        }
    }
}